SSL的作用

实现客户端和服务器之间的安全通讯(加密和完整性校验)

配置安装源

yum install -y https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

配置安装包

yum install -y mysql-community-server mysql-community-devel mysql-community-client

启动数据库

systemctl start mysqld
systemctl enable mysqld

初始化数据库

获取临时密码

cat /var/log/mysqld.log | grep 'A temporary password'

初始化数据库mysql_secure_installation

mysql_secure_installation

Enter password for user root:ufqLq&R6tgl%
[...]
New password:*******
Re-enter new password:*******
[...]
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password:*******
Re-enter new password:*******
[...]
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
[...]
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
[...]
Disallow root login remotely? (Press y|Y for Yes, any other key for No) :n
[...]
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
[...]
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
[...]

关闭密码复杂度要求和配置默认编码为utf8

cp /etc/my.cnf /etc/my.cnf.default
vim /etc/my.cnf

加入

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'

重启数据库服务

systemctl restart mysqld

允许远程登录

mysql -uroot -p

use mysql
update user set host='%' where user = 'root';
flush privileges;

确保本机安装SSL

查询MySQL是基于那种SSL

mysql -uroot -p

show status like 'rsa_public_key';

返回如下提示：
Empty set (0.00 sec)
以上表明官方的编译基于yaSSL，如果是基于openSSL，以下命令查看openSSL的版本
openssl version

生成所需的证书

mysql_ssl_rsa_setup

ls -l /var/lib/mysql/*.pem

MySQL配置文件中开启SSL

vim /etc/my.cnf

添加
ssl-ca = /var/lib/mysql/ca.pem 
ssl-cert = /var/lib/mysql/server-cert.pem 
ssl-key = /var/lib/mysql/server-key.pem

重启服务
systemctl restart mysqld

确认是否开启SSL

mysql -uroot -p

show global variables like 'have_%ssl';
显示如下：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+
2 rows in set (0.00 sec)

查看SSL的加密方式

mysql -uroot -p
show global variables like 'tls_version';
显示如下：

+---------------+---------------+
| Variable_name | Value         |
+---------------+---------------+
| tls_version   | TLSv1,TLSv1.1 |
+---------------+---------------+
1 row in set (0.00 sec)

客户端连接测试

mysql -uroot -h 192.168.1.2 -p

最好使用远程连接进行测试，localhost或者-S unix socket连接，这种有可能不会用ssl。

mysql> status
--------bin/mysql  Ver 14.14 Distrib 5.7.9, for Linux (x86_64) using  EditLine wrapper

Connection id:      10
Current database:   
Current user:       root@192.168.1.2
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;

status中SSL中显示Cipher in use,表明当前连接使用ssl

必须使用ssl登录的用户

更改mysql.user表　把ssl_type设置成ANY就好了

需要ssl
alter user 'root'@'%' require ssl;
不需要ssl
alter user 'ssltest'@'%' require none;